﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class InterestRatesDBAccess
    {
        /// <summary>
        /// Insert InterestRates
        /// </summary>
        /// <param name="objInterestRates"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool AddNewInterestRates(InterestRates objInterestRates, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
                //new SqlParameter("@InterestRatesID", objInterestRates.InterestRatesID),
                new SqlParameter("@Created", objInterestRates.Created),
                new SqlParameter("@Updated",objInterestRates.Updated),
                new SqlParameter("@Isactive", objInterestRates.Isactive),
                new SqlParameter("@RateType", objInterestRates.RateType),            
                new SqlParameter("@InterestRate", objInterestRates.InterestRate),
                new SqlParameter("@Descriptions", objInterestRates.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("AddNewInterestRates", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Update InterestRates
        /// </summary>
        /// <param name="objInterestRates"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateInterestRates(InterestRates objInterestRates, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@InterestRateID", objInterestRates.InterestRatesID),
                new SqlParameter("@Created", objInterestRates.Created),
                new SqlParameter("@Updated",objInterestRates.Updated),
                new SqlParameter("@Isactive", objInterestRates.Isactive),
                new SqlParameter("@RateType", objInterestRates.RateType),            
                new SqlParameter("@InterestRate", objInterestRates.InterestRate),
                new SqlParameter("@Descriptions", objInterestRates.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateInterestRates", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Delete InterestRates
        /// </summary>
        /// <param name="InterestRatesID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteInterestRates(int InterestRatesID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@InterestRatesID", InterestRatesID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteInterestRates", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Get InterestRates Details
        /// </summary>
        /// <param name="InterestRatesID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public InterestRates GetInterestRatesDetails(string InterestRatesID, String CONNECTION_STRING)
        {
            InterestRates objInterestRates = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@InterestRateID", InterestRatesID),
            };
            //Lets get the list of all InterestRates in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetInterestRatesDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of InterestRates
                    objInterestRates = new InterestRates();

                    //Now lets populate InterestRates details into the list of InterestRates                                       
                    //objInterestRates.InterestRatesID = Convert.ToInt32(row["InterestRateID"]);
                    objInterestRates.Created = row["Created"].ToString();
                    objInterestRates.Updated = row["Updated"].ToString();
                    objInterestRates.Isactive = row["Isactive"].ToString();
                    objInterestRates.RateType = row["RateType"].ToString();
                    objInterestRates.InterestRate = float.Parse(row["InterestRate"].ToString());
                    objInterestRates.Descriptions = row["Descriptions"].ToString();
                
                }
            }

            return objInterestRates;
        }


        /// <summary>
        /// Get InterestRates Details
        /// </summary>
        /// <param name="Rate Type"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public InterestRates GetInterestRates(string rateType, String CONNECTION_STRING)
        {
            InterestRates objInterestRates = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@ratetype", rateType),
            };
            //Lets get the list of all InterestRates in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetInterestRates", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of InterestRates
                    objInterestRates = new InterestRates();

                    //Now lets populate InterestRates details into the list of InterestRates                                       
                    objInterestRates.InterestRatesID = Convert.ToInt32(row["InterestRateID"]);
                    objInterestRates.Created = row["Created"].ToString();
                    objInterestRates.Updated = row["Updated"].ToString();
                    objInterestRates.Isactive = row["Isactive"].ToString();
                    objInterestRates.RateType = row["RateType"].ToString();
                    objInterestRates.InterestRate = float.Parse(row["InterestRate"].ToString());
                    objInterestRates.Descriptions = row["Descriptions"].ToString();

                }
            }

            return objInterestRates;
        }


        /// <summary>
        /// Get InterestRates List
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<InterestRates> GetInterestRatesList(String CONNECTION_STRING)
        {
            List<InterestRates> listInterestRates = null;

            //Lets get the list of all InterestRates in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetInterestRatesList", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of InterestRates
                    listInterestRates = new List<InterestRates>();

                    //Now lets populate InterestRates details into the list of InterestRates
                    foreach (DataRow row in table.Rows)
                    {
                        InterestRates objInterestRates = new InterestRates();
                        objInterestRates.InterestRatesID = Convert.ToInt32(row["InterestRateID"]);
                        objInterestRates.Created = row["Created"].ToString();
                        objInterestRates.Updated = row["Updated"].ToString();
                        objInterestRates.Isactive = row["Isactive"].ToString();
                        objInterestRates.RateType = row["RateType"].ToString();
                        objInterestRates.InterestRate = float.Parse(row["InterestRate"].ToString());
                        objInterestRates.Descriptions = row["Descriptions"].ToString();

                        listInterestRates.Add(objInterestRates);
                    }
                }
            }

            return listInterestRates;
        }        
    }
}
